import pymysql
import sys
import datetime
from docx import Document

# MySQL巡检工具，生成word版的巡检报告
class MySQLMonitor():
    # 构造方法，接收数据库连接信息
    def __init__(self, host=None, user=None, password=None, database=None, port=3306):
        self.host = host
        self.user = user
        self.password = password
        self.database = database
        self.port = port

    # 创建数据库连接
    def dbconn(self):
        try:
            dbconn = pymysql.connect(host=self.host, user=self.user, password=self.password, database=self.database, port=self.port)
        except Exception as e:
            print("数据库连接失败，检查数据库地址、端口是否正确")
            print(e)
            sys.exit()
        cr = dbconn.cursor()

        return cr, dbconn

    # 获取数据库运行状态数据, {"巡检指标":"数据"}
    def get_status(self):
        cr, dbconn = self.dbconn()

        status = {}

        # 获取数据库运行时间
        uptime_sql = "show global status like 'Uptime'"
        cr.execute(uptime_sql)
        uptime = cr.fetchone()
        status["运行时长"] = uptime[1]

        # 获取连接数
        conn_sql = "show global status like 'Threads_connected'"
        cr.execute(conn_sql)
        connected = cr.fetchone()
        status["连接数"] = connected[1]

        # 获取数据库Innodb缓存容量
        innodb_buffer_pool_size_sql = "show variables like 'innodb_buffer_pool_size'"
        cr.execute(innodb_buffer_pool_size_sql)
        innodb_buffer_pool_size = cr.fetchone()
        status["Innodb缓存容量"] = innodb_buffer_pool_size[1]

        # 获取数据库事务数
        trx_sql = "show global status like 'Com_commit'"
        cr.execute(trx_sql)
        trx = cr.fetchone()
        status["事务数"] = trx[1]

        cr.close()
        dbconn.close()

        return status

    # 生成word版巡检报告
    def write_dict_to_word(self, status_dict, word):
        # 创建Word文档对象
        doc = Document()

        # 添加标题， 巡检人: xxxxx   巡检时间： xxxxxx
        now = datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')
        title = "巡检人: Martin     巡检时间: %s" % now
        doc.add_heading(title, level=1)

        # 创建一行两列的表格
        table = doc.add_table(rows=1, cols=2)
        # 设置表格样式
        table.style = 'Table Grid'

        # 获取表格第一行
        row = table.rows[0]
        # 设置表头
        row.cells[0].text = '监控指标'
        row.cells[1].text = '运行数据'

        # 遍历字典，为每个键值对添加一行
        for key, value in status_dict.items():
            # 添加新行
            new_row = table.add_row()
            # 写入键和值
            new_row.cells[0].text = str(key)
            new_row.cells[1].text = str(value)

        # 保存文档
        doc.save(word)


if __name__ == '__main__':
    p1 = MySQLMonitor(host="192.168.140.10", user="admin", password="123456", database="it", port=3306)

    status = p1.get_status()

    now = datetime.datetime.now().strftime('%Y_%m_%d_%H_%M_%S')
    p1.write_dict_to_word(word="MySQL_report_%s.docx" % now, status_dict=status)



